const { Pool } = require('pg');
require('dotenv').config({ path: '../backend/.env' });

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
  client_encoding: 'utf8'
});

async function checkPermissions() {
  try {
    console.log('Connecting to database...');
    
    // Test connection
    const client = await pool.connect();
    console.log('✓ Connected to database successfully');
    
    // Check current user
    const userResult = await client.query('SELECT current_user;');
    console.log('Current user:', userResult.rows[0].current_user);
    
    // First, create a test user if it doesn't exist
    const userCheck = await client.query('SELECT id FROM users WHERE id = $1', [1]);
    if (userCheck.rows.length === 0) {
      await client.query(
        'INSERT INTO users (id, username, phone, password_hash) VALUES ($1, $2, $3, $4)',
        [1, 'testuser', '13737725717', 'test_hash']
      );
      console.log('✓ Created test user');
    } else {
      console.log('✓ Test user already exists');
    }
    
    // Check table permissions
    const tables = ['users', 'categories', 'bills', 'budgets', 'reports', 'ai_suggestions'];
    
    for (const table of tables) {
      try {
        // Test SELECT
        await client.query(`SELECT COUNT(*) FROM ${table} LIMIT 1;`);
        console.log(`✓ SELECT permission on ${table}`);
        
        // Test INSERT (with a dummy record that we'll immediately delete)
        if (table === 'categories') {
          const insertResult = await client.query(
            `INSERT INTO ${table} (user_id, name, type, icon, color, is_default) 
             VALUES ($1, $2, $3, $4, $5, $6) RETURNING id`,
            [1, 'Test Category', 'expense', 'test', '#000000', false]
          );
          const categoryId = insertResult.rows[0].id;
          console.log(`✓ INSERT permission on ${table}`);
          
          // Test UPDATE
          await client.query(`UPDATE ${table} SET name = $1 WHERE id = $2`, ['Updated Test', categoryId]);
          console.log(`✓ UPDATE permission on ${table}`);
          
          // Test DELETE
          await client.query(`DELETE FROM ${table} WHERE id = $1`, [categoryId]);
          console.log(`✓ DELETE permission on ${table}`);
        } else if (table === 'bills') {
          // First create a category for the bill
          const categoryResult = await client.query(
            `INSERT INTO categories (user_id, name, type, icon, color, is_default) 
             VALUES ($1, $2, $3, $4, $5, $6) RETURNING id`,
            [1, 'Test Category', 'expense', 'test', '#000000', false]
          );
          const categoryId = categoryResult.rows[0].id;
          
          const insertResult = await client.query(
            `INSERT INTO ${table} (user_id, category_id, title, amount, type, bill_date) 
             VALUES ($1, $2, $3, $4, $5, $6) RETURNING id`,
            [1, categoryId, 'Test Bill', 100.00, 'expense', '2025-09-26']
          );
          const billId = insertResult.rows[0].id;
          console.log(`✓ INSERT permission on ${table}`);
          
          // Test UPDATE
          await client.query(`UPDATE ${table} SET amount = $1 WHERE id = $2`, [200.00, billId]);
          console.log(`✓ UPDATE permission on ${table}`);
          
          // Test DELETE
          await client.query(`DELETE FROM ${table} WHERE id = $1`, [billId]);
          console.log(`✓ DELETE permission on ${table}`);
          
          // Delete the category
          await client.query(`DELETE FROM categories WHERE id = $1`, [categoryId]);
        } else {
          console.log(`✓ Basic SELECT permission on ${table}`);
        }
      } catch (error) {
        console.error(`✗ Permission error on ${table}:`, error.message);
      }
    }
    
    client.release();
    console.log('All permission checks completed!');
  } catch (error) {
    console.error('Database connection error:', error.message);
  } finally {
    await pool.end();
  }
}

checkPermissions();